package light.head.dao.oa;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import light.head.constant.Parm;
import light.head.constant.Sys;
import light.head.enm.RESULT;
import light.head.framework.base.BaseDAO;
import light.head.framework.entity.PageBean;
import light.head.framework.utils.DateUtils;
import light.head.model.AuditStatus;
import light.head.model.Reimburse;
import light.head.model.ReimburseItem;

import org.springframework.stereotype.Service;

/***
 * 物品申请
 * @author yy
 *
 */
@Service
public class ReimburseDAO extends BaseDAO<Reimburse, Integer>{
	
	private final static String sql_findApplyItem = " FROM ReimburseItem ad WHERE ad.rbc = ?";
	private final static String sql_findAuditStatusBusiness = " FROM AuditStatus a WHERE a.mc = ? AND a.rc = ? AND a.business = ?";
	private final static String sql_findByMcAuditStatus = " FROM AuditStatus a WHERE a.mc = ? ";
	private final static String sql_findCu = " FROM Reimburse a WHERE a.cu = ?";
	
	/***
	 * 根据跟机构及子机构编号查询申请单
	 * @param ocs 跟机构及子机构编号
	 * @param map
	 * @param pb
	 * @return
	 */
	public RESULT findByMap(List<Integer> ocs, Map<String, String> map , PageBean pb){
		if (null == ocs || ocs.size() == 0) return null;
		
		StringBuffer hql = new StringBuffer("FROM Reimburse a WHERE a.oc IN (");
		List<Object> list = new ArrayList<Object>();
		for (Integer i : ocs) {
			hql.append("?,");
			list.add(i);
		}
		hql.deleteCharAt(hql.length() - 1).append(")");
		if(map != null){
			String deleted = map.get(Parm.DELETED);
			String time1 = map.get(Parm.TIME1);
			String time2 = map.get(Parm.TIME2);
			String title = map.get(Parm.TITLE);
			String status = map.get(Parm.STATUS);
			if (isNotNull(deleted)) {
				list.add(Boolean.parseBoolean(deleted));
				hql.append(" AND a.deleted = ?");
			}
			if (isNotNull(status)) {
				list.add(Byte.parseByte(status));
				hql.append(" AND a.status = ?");
			}
			if (isNotNull(time1)) {
				list.add(DateUtils.getDateByStr(time1, null));
				hql.append(" AND a.ct >= ?");
			}
			if(isNotNull(time2)){
				list.add(DateUtils.getDateByStr(time2, null));
				hql.append("AND a.ct <= ?");
			}
			if(isNotNull(title)) {
				list.add("%" + title + "%");
				hql.append("AND a.title LIKE ?");
			}
			
		}	 
		hql.append(" AND (1 = (SELECT s.status FROM AuditStatus s WHERE s.business = 4 and s.mc = a.code and a.sort = 2 and s.pc = (SELECT max(pc) FROM AuditStatus where business = 4 and mc = s.mc))OR a.sort = 1)");
		hql.append(" ORDER BY a.code DESC");
		return findToPageBean(hql.toString(), pb, list);
	}
	/***
	 * 根据当前用户查询申请单
	 * @param au 当前用户编号
	 * @param map
	 * @param pb
	 * @return
	 */
	public RESULT findByMap_2(Integer au, Map<String, String> map , PageBean pb){
		if (null == au) return null;
		
		StringBuffer hql = new StringBuffer("FROM Reimburse a WHERE a.cu = ?");
		List<Object> list = new ArrayList<Object>();
		
		list.add(au);
		if (map != null) {
			String deleted = map.get(Parm.DELETED);
			String time1 = map.get(Parm.TIME1);
			String time2 = map.get(Parm.TIME2);
			String title = map.get(Parm.TITLE);
			String status = map.get(Parm.STATUS);
			if (isNotNull(deleted)) {
				list.add(Boolean.parseBoolean(deleted));
				hql.append(" AND a.deleted = ?");
			}
			if (isNotNull(status)) {
				list.add(Byte.parseByte(status));
				hql.append(" AND a.status = ?");
			}
			if (isNotNull(time1)) {
				list.add(DateUtils.getDateByStr(time1, null));
				hql.append(" AND a.ct >= ?");
			}
			if (isNotNull(time2)) {
				list.add(DateUtils.getDateByStr(time2, null));
				hql.append("AND a.ct <= ?");
			}
			if (isNotNull(title)) {
				list.add("%" + title + "%");
				hql.append("AND a.title LIKE ?");
			}
		}	 
		hql.append(" ORDER BY a.code DESC");
		return findToPageBean(hql.toString(), pb, list);
	}
	/**
	 * 统计
	 */
	public List<Object> findSuc(List<Integer> ocs, String suc ,Map<String, String> map){
		if (null == ocs || null == suc) return null;
		List<Object> parm = new ArrayList<Object>();
		StringBuffer temp = new StringBuffer("SELECT item.rcc,sum(amount) FROM ReimburseItem item");
		temp.append(" WHERE item.rcc IN(" + suc + ") AND item.rbc IN (");
		if (map != null) {
			String oc =map.get(Parm.OC);
			String time1 = map.get(Parm.TIME1);
			String time2 = map.get(Parm.TIME2);
			
			if(isNotNull(oc)) {
				parm.add(Integer.parseInt(oc));
				temp.append(" SELECT r.code FROM Reimburse r WHERE r.oc = ?");
			}else{
				temp.append("SELECT r.code FROM Reimburse r WHERE r.oc IN(");
				for (Integer ig : ocs) {
					temp.append("?,");
					parm.add(ig);
				}
				temp.deleteCharAt(temp.length() - 1).append(")");
			}
			if (isNotNull(time1)) {
				parm.add(DateUtils.getDateByStr(time1, null));
				temp.append(" AND r.ct >= ?");
			}
			if(isNotNull(time2)){
				parm.add(DateUtils.getDateByStr(time2, null));
				temp.append(" AND r.ct <= ?");
			}
		}
		temp.append(" ) GROUP BY item.rcc");
		return find(temp.toString(), parm.toArray());
	}
	
	
	public String findCode(Integer code){
		if (null == code) return null;
		List<Object> list = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer("SELECT rc.name FROM ReimburseCategory rc WHERE rc.code = ?");
		list.add(code);
		
		List<Object> objList = query(sql.toString(), null, 1, list.toArray());	
		if (null == objList || objList.size() == 0) 
			return null;
		
		return (null != objList.get(0) && !Sys.NONE.equals(objList.get(0))) ? objList.get(0).toString() : null;
	}
	/**
	 * 审核申请单(查询出待审核的物品申请单：1.上一级审核已经通过的， 2.本机构员工所提交的)
	 * @param ocs	当前机构的跟机构下的所有子机构集合
	 * @param rc	角色编号
	 * @param map	查询条件
	 * @param pb	分页信息
	 */
	public RESULT findByMap_1(List<Integer> ocs, Integer rc, Map<String, String> map , PageBean pb){
		if (null == ocs || null == rc) return null;
		
		List<Object> parm = new ArrayList<Object>();	
		List<Object> countParm = new ArrayList<Object>();
		
		StringBuffer temp = new StringBuffer();
		temp.append(" FROM Reimburse a");
		temp.append(" JOIN UserInfo u ON u.code = a.cu");
		temp.append(" JOIN Organ o ON o.code = a.oc");
		temp.append(" JOIN AuditStatus s on s.mc = a.code and s.beforeStatus = 1 WHERE a.deleted = 0 AND s.business = 4 AND s.rc = ? ");
		
		parm.add(rc);
		countParm.add(rc);
		
		if(map != null){
			String deleted = map.get(Parm.DELETED);
			String time1 = map.get(Parm.TIME1);
			String time2 = map.get(Parm.TIME2);
			String title = map.get(Parm.TITLE);
			String oc = map.get(Parm.OC);
			String project = map.get(Parm.PROJECT);
			String cn = map.get(Parm.CN);
			String pass = map.get(Parm.PASS);
			String suc = map.get(Parm.SUC);
			if (isNotNull(deleted)) {
				parm.add(Boolean.parseBoolean(deleted));
				countParm.add(Boolean.parseBoolean(deleted));
				temp.append(" AND a.deleted = ?");
			}
			if (isNotNull(time1)) {
				parm.add(DateUtils.getDateByStr(time1, null));
				countParm.add(DateUtils.getDateByStr(time1, null));
				temp.append(" AND a.ct >= ?");
			}
			if(isNotNull(time2)){
				parm.add(DateUtils.getDateByStr(time2, null));
				countParm.add(DateUtils.getDateByStr(time2, null));
				temp.append(" AND a.ct <= ?");
			}
			if(isNotNull(title)) {
				parm.add("%" + title + "%");
				countParm.add("%" + title + "%");
				temp.append(" AND a.title LIKE ?");
			}
			if(isNotNull(oc)) {
				parm.add(Integer.parseInt(oc));
				countParm.add(Integer.parseInt(oc));
				temp.append(" AND a.oc = ?");
			}else{
				temp.append("and a.oc IN (");
				for (Integer ig : ocs) {
					temp.append("?,");
					parm.add(ig);
					countParm.add(ig);
				}
				temp.deleteCharAt(temp.length() - 1).append(")");
			}
			if(isNotNull(cn)) {
				parm.add("%" + cn + "%");
				countParm.add("%" + cn + "%");
				temp.append(" AND a.cu IN (SELECT user.code FROM UserInfo user WHERE user.realName LIKE ?)");
			}
			if(isNotNull(pass)) {
				parm.add(Boolean.parseBoolean(pass));
				countParm.add(Boolean.parseBoolean(pass));
				temp.append(" AND a.pass = ?");
			}
			if(isNotNull(project)) {
				parm.add("%" + project + "%");
				countParm.add("%" + project + "%");
				temp.append(" AND a.code IN (SELECT ri.rbc FROM ReimburseItem ri WHERE ri.project LIKE ?)");
			}
			if (isNotNull(suc)) {
				temp.append(" AND a.code IN (SELECT ri.rbc FROM ReimburseItem ri WHERE ri.rcc IN (" + suc + "))");
			}
		}	 
		
		StringBuffer sql = new StringBuffer("SELECT a.code, a.title, a.cu, a.ct, a.oc, a.deleted, u.realName, o.name");
		StringBuffer countSql = new StringBuffer("SELECT COUNT(*)");
		
		sql.append(temp).append(" ORDER BY a.code DESC");
		countSql.append(temp);
		
		if (RESULT.SUCCESS == queryToPageBean(pb, sql.toString(), parm, countSql.toString(), countParm))
			return packageResult(pb);
		else
			return RESULT.ERROR;
		}
	/***
	 * 封装
	 * @param pb
	 * @return
	 */
	private RESULT packageResult(PageBean pb) {
		
		Reimburse info = null;
		List<Reimburse> list = new ArrayList<Reimburse>();
		
		//  a.code, a.title, a.au, a.at, a.oc, a.deleted, u.realName, o.name
		for (Object o : pb.getResultList()) {
			
			info = new Reimburse();
			Object[] obj = (Object[]) o;
			
			Integer code = (null == obj[0] || Sys.NONE.equals(obj[0].toString())) ? null : (Integer) obj[0];
			String title = (null == obj[1] || Sys.NONE.equals(obj[1].toString())) ? null : (String) obj[1];
			Integer au = (null == obj[2] || Sys.NONE.equals(obj[2].toString())) ? null : (Integer) obj[2];
			Date at = (null == obj[3] || Sys.NONE.equals(obj[3].toString())) ? null : (Date) obj[3];
			Integer oc = (null == obj[4] || Sys.NONE.equals(obj[4].toString())) ? null : (Integer) obj[4];
			Boolean deleted = (null == obj[5] || Sys.NONE.equals(obj[5].toString())) ? null : (Boolean) obj[5];
			String an = (null == obj[6] || Sys.NONE.equals(obj[6].toString())) ? null : (String) obj[6];
			String ocn = (null == obj[7] || Sys.NONE.equals(obj[7].toString())) ? null : (String) obj[7];
			
			info.setCode(code);
			info.setTitle(title);
			info.setCu(au);
			info.setCt(at);
			info.setOc(oc);
			info.setDeleted(deleted);
			info.setCn(an);
			info.setOn(ocn);
			
			list.add(info);
		}
		pb.setResultList(list);
		return RESULT.SUCCESS;
	}
	
  /***
   * 根据申请单编号查询申请明细
   * @param code
   * @return
   */
	public List<ReimburseItem> findApplyItem(Integer code) {
		if (null == code)return null;
		List<ReimburseItem> list = find(sql_findApplyItem, code);
		return list;
	}
	
	/***
	 * 
	 * @param mc
	 * @param rc
	 * @return
	 */
	public List<AuditStatus> findByMcAuditStatus(Integer mc) {
		if (null == mc )return null;
		return find(sql_findByMcAuditStatus, null, null, mc);
	}
	
	/***
	 * 
	 * @param mc
	 * @param rc
	 * @return
	 */
	public List<AuditStatus> findAuditStatusBusiness(Integer mc, Integer rc, Byte business) {
		if (null == mc || null == rc)return null;
		return find(sql_findAuditStatusBusiness, null, null, mc, rc, business);
	}
	
	/***
	 * 查询当前登录人的所有申请信息
	 * @param cu
	 * @return
	 */
	public List<Reimburse> findCu(Integer cu) {
		if (null == cu || null == cu)return null;
		return find(sql_findCu, null, null, cu);
	}
	/***
	 * 导出
	 * @param cu
	 * @return
	 */
	public List<ReimburseItem> findCode(List<Integer> ocs,String time1,String time2,String oc,String suc) {
		StringBuffer sql = new StringBuffer("from ReimburseItem ri WHERE ri.rbc IN (select re.code FROM Reimburse re WHERE re.pass = 3 ");
		List<Object> parm = new ArrayList<Object>();
		
		if(isNotNull(time1)){
			sql.append(" AND re.pt > ?");
			parm.add(DateUtils.getDateByStr(time1, "yyyy-MM-dd"));
		}
		if(isNotNull(time2)){
			sql.append(" AND re.pt < ?");
			parm.add(DateUtils.getDateByStr(time2, "yyyy-MM-dd"));
		}
		if(isNotNull(oc)){
			sql.append(" AND re.oc = ?");
			parm.add(Integer.parseInt(oc));
		}else{
			sql.append("AND re.oc IN (");
			for (Integer i : ocs) {
				sql.append("?,");
				parm.add(i);
			}
			sql.deleteCharAt(sql.length() - 1).append(")");
		}
		sql.append(")");
		if (isNotNull(suc)) {
			sql.append(" AND ri.rcc IN (" + suc + ")");
		}
		return find(sql.toString(), parm.toArray());
	}
	
	
	/***
	 * 统计
	 * @param cu
	 * @return
	 */
	public RESULT findCode(List<Integer> ocs,Map<String, String> map , PageBean pb) {
		StringBuffer sql = new StringBuffer("from ReimburseItem ri WHERE ri.rbc IN (select re.code FROM Reimburse re WHERE re.pass = 3 ");
		List<Object> parm = new ArrayList<Object>();
		if (map != null){
			String suc = map.get(Parm.SUC);
			String time1 = map.get(Parm.TIME1);
			String time2 = map.get(Parm.TIME2);
			String oc = map.get(Parm.OC);
			if(isNotNull(time1)){
				sql.append(" AND re.pt > ?");
				parm.add(DateUtils.getDateByStr(time1, "yyyy-MM-dd"));
			}
			if(isNotNull(time2)){
				sql.append(" AND re.pt < ?");
				parm.add(DateUtils.getDateByStr(time2, "yyyy-MM-dd"));
			}
			if(isNotNull(oc)){
				sql.append(" AND re.oc = ?");
				parm.add(Integer.parseInt(oc));
			}else{
				sql.append("AND re.oc IN (");
				for (Integer i : ocs) {
					sql.append("?,");
					parm.add(i);
				}
				sql.deleteCharAt(sql.length() - 1).append(")");
			}
			sql.append(")");
			if (isNotNull(suc)) {
				sql.append(" AND ri.rcc IN (" + suc + ")");
			}
		}
		return findToPageBean(sql.toString(), pb, parm);
	}
	
	
	/**彭总专用
	 * 审核申请单(查询出待审核的物品申请单：1.上一级审核已经通过的， 2.本机构员工所提交的)
	 * @param ocs	当前机构的跟机构下的所有子机构集合
	 * @param rc	角色编号
	 * @param map	查询条件
	 * @param pb	分页信息
	 */
	public RESULT findByMap(List<Integer> ocs, Integer rc, Map<String, String> map , PageBean pb){
		if (null == ocs || null == rc) return null;
		List<Object> parm = new ArrayList<Object>();	
		List<Object> countParm = new ArrayList<Object>();
		
		StringBuffer temp = new StringBuffer();
		temp.append(" FROM Reimburse a");
		temp.append(" JOIN UserInfo u ON u.code = a.cu");
		temp.append(" JOIN Organ o ON o.code = a.oc");
		temp.append(" JOIN AuditStatus s on s.mc = a.code WHERE a.deleted = 0 AND s.business = 4 AND s.rc = ? and a.oc IN (");
		
		parm.add(rc);
		countParm.add(rc);
		for (Integer ig : ocs) {
			temp.append("?,");
			parm.add(ig);
			countParm.add(ig);
		}
		
		temp.deleteCharAt(temp.length() - 1).append(")");
		
		if(map != null){
			String deleted = map.get(Parm.DELETED);
			String time1 = map.get(Parm.TIME1);
			String time2 = map.get(Parm.TIME2);
			String title = map.get(Parm.TITLE);
			String on = map.get(Parm.ON);
			String project = map.get(Parm.PROJECT);
			String cn = map.get(Parm.CN);
			String pass = map.get(Parm.PASS);
			if (isNotNull(deleted)) {
				parm.add(Boolean.parseBoolean(deleted));
				countParm.add(Boolean.parseBoolean(deleted));
				temp.append(" AND a.deleted = ?");
			}
			if (isNotNull(time1)) {
				parm.add(DateUtils.getDateByStr(time1, null));
				countParm.add(DateUtils.getDateByStr(time1, null));
				temp.append(" AND a.ct >= ?");
			}
			if(isNotNull(time2)){
				parm.add(DateUtils.getDateByStr(time2, null));
				countParm.add(DateUtils.getDateByStr(time2, null));
				temp.append(" AND a.ct <= ?");
			}
			if(isNotNull(title)) {
				parm.add("%" + title + "%");
				countParm.add("%" + title + "%");
				temp.append(" AND a.title LIKE ?");
			}
			if(isNotNull(on)) {
				parm.add("%" + on + "%");
				countParm.add("%" + on + "%");
				temp.append(" AND a.oc IN (SELECT organ.code FROM Organ organ WHERE organ.name LIKE ?)");
			}
			if(isNotNull(cn)) {
				parm.add("%" + cn + "%");
				countParm.add("%" + cn + "%");
				temp.append(" AND a.cu IN (SELECT user.code FROM UserInfo user WHERE user.realName LIKE ?)");
			}
			if(isNotNull(pass)) {
				parm.add(Boolean.parseBoolean(pass));
				countParm.add(Boolean.parseBoolean(pass));
				temp.append(" AND a.pass = ?");
			}
			if(isNotNull(project)) {
				parm.add("%" + project + "%");
				countParm.add("%" + project + "%");
				temp.append(" AND a.code IN (SELECT ri.rbc FROM ReimburseItem ri WHERE ri.project LIKE ?)");
			}
		}	 
		
		StringBuffer sql = new StringBuffer("SELECT a.code, a.title, a.cu, a.ct, a.oc, a.deleted, u.realName, o.name");
		StringBuffer countSql = new StringBuffer("SELECT COUNT(*)");
		
		sql.append(temp).append(" ORDER BY a.code DESC");
		countSql.append(temp);
		
		if (RESULT.SUCCESS == queryToPageBean(pb, sql.toString(), parm, countSql.toString(), countParm))
			return packageResult(pb);
		else
			return RESULT.ERROR;
		}
	/**
	 * 根据条件分页查询报销项目信息
	 * @param	查询条件
	 * @param	分页对象
	 */
	public RESULT findSubUsers(List<Integer> ocs, Map<String, String> map, PageBean pageBean) {
		StringBuffer hql = new StringBuffer("FROM ReimburseCategory rc WHERE  rc.oc IN(");
		List<Object> list =  new ArrayList<Object>();
		for (Integer i : ocs) {
			hql.append("?,");
			list.add(i);
		}
		hql.deleteCharAt(hql.length() - 1).append(")");
		if (map != null) {
			String name = map.get(Parm.NAME);
			String oc = map.get(Parm.OC);
			String suc = map.get(Parm.SUC);

			if (isNotNull(name)) {
				hql.append(" AND rc.name LIKE ?");
				list.add("%" + name + "%");
			}
			if (isNotNull(oc)) {
				hql.append(" AND rc.oc = ?");
				list.add(Integer.parseInt(oc));
			}
			if (isNotNull(suc)) {
				hql.append(" AND rc.code NOT IN (" + suc + ")");
			}
		}
		hql.append(" ORDER BY rc.code DESC");
		
		return findToPageBean(hql.toString(), pageBean, list);
	}

}
